/**
 * 
 */
package herguan.expertadvise.dao;

/**
 * @author Nidhi Pandya
 *
 */

import herguan.expertadvise.dao.hibernate.Login;
import herguan.expertadvise.dao.hibernate.Skill;
import herguan.expertadvise.vo.UsersListVO;

import java.util.ArrayList;
import java.util.Collection;

import java.util.*;
import java.sql.*;

import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.*;

public class SpringHibernateDAOImpl extends HibernateDaoSupport implements
SpringHibernateDAO {


	//check admin login 



	public boolean checkUserLogin(String strUserName, String strPassword) throws DataAccessException,java.sql.SQLException
	{
		boolean valid = false;
		Connection conn = this.getSession().connection();
		//Write jdbc code to validate the user against database
		Statement smt = conn.createStatement();
		ResultSet rs;
		//write select query for checking password 
		String query="select userid from login where username='"+strUserName+"' and password='"+strPassword+"'";
		rs=smt.executeQuery(query);

		if(rs.next()== true){
			valid=true;

		}else{
			valid=false;
		}

		smt.close();
		rs.close();
		conn.close();

		return valid;


	}


	public void addUser(Login obj) throws DataAccessException{
		getHibernateTemplate().save(obj);
	}
	public void addratings(String userid, String ratings) throws DataAccessException,java.sql.SQLException
	{
		Connection conn = this.getSession().connection();
		//Write jdbc code to validate the user against database
		Statement smt = conn.createStatement();
		ResultSet rs;
		//write select query for checking password 
		String query="Select avg_rating from provider where userid ="+userid+"" ;
		rs=smt.executeQuery(query);
		int avg_rating =0;
		while(rs.next()){			
			avg_rating=rs.getInt("avg_rating"); 
			
			// This Will return 5
		}
		
		double newRating = (avg_rating + Integer.parseInt(ratings))/2;
		
		String queryUpdate ="UPDATE  provider SET  avg_rating =" + newRating +
							" where userid ="+userid+"" ;
		
		smt.executeQuery(query);
		
	}

	public void updateUser(Login obj) throws DataAccessException{
		getHibernateTemplate().update(obj);
	}
public void foo1()
{
System.out.println("Foo");	
}

public String foo2()
{
System.out.println("Foo2");
return "abc";
}

public void foo3(UsersListVO Nidhi )
{
System.out.println("Foo3");

}

public Login foo4 (String Nidhi )
{
System.out.println("Foo4");
Login objLogin = new Login();
return  objLogin;

}

public int foo5( )
{
return 15;

}




	public Login loadUser(String id) throws DataAccessException{
		//return getHibernateTemplate().find("from herguanlocal.dao.hibernate.Article obj where obj.id = '" + id + "'");
		return (Login) getHibernateTemplate().get(Login.class,new Integer(id));
	}
	
	public ArrayList<Skill> loadSkills() throws DataAccessException,java.sql.SQLException{
		
		 ArrayList<Skill> arrSkills= new ArrayList();
		 Connection conn = this.getSession().connection();

			//Write jdbc code 
			Statement smt = conn.createStatement();

			ResultSet rs=null;

			ArrayList list = new ArrayList();

			String query="SELECT skillid,skillname FROM skill";



			rs=smt.executeQuery(query);



			while(rs.next()){

				Skill objSkill =new Skill();

				int skillid=rs.getInt("skillid");
				objSkill.setSkillid(skillid);

				String skillname=rs.getString("skillname");
				objSkill.setSkillname(skillname);

				arrSkills.add(objSkill);

			}

			rs.close();
			smt.close();
			conn.close();

		 
		 return arrSkills;
	}
	
	

	public boolean checkValidUserName(String strUserid) throws DataAccessException,java.sql.SQLException{
		boolean valid = false;
		Connection conn = this.getSession().connection();
		//Write jdbc code to validate the user against database
		Statement smt = conn.createStatement();
		ResultSet rs;
		//write select query for checking password 

		String query="select userid from login where username='"+strUserid+"'";
		rs=smt.executeQuery(query);


		if(rs.next() == true){

			valid=true;

		}else{
			valid=false;
		}

		smt.close();
		rs.close();
		conn.close();


		return valid;
	}


	//get latest jobs
	public Collection getUsersList(ArrayList<String> arrSelectedSkils)throws DataAccessException,java.sql.SQLException{



		Connection conn = this.getSession().connection();

		//Write jdbc code 
		Statement smt = conn.createStatement();

		ResultSet rs=null;

		ArrayList<UsersListVO> list = new ArrayList<UsersListVO>();

		String query= "Select DISTINCT l.username,l.userid   FROM userskill u "+ 
					  " INNER JOIN login l On l.userid = u.userid"+
					  " INNER JOIN skill s ON u.skillid = s.skillid "+
					  " WHERE 1=1 AND s.skillid IN (1,2)";



		rs=smt.executeQuery(query);



		while(rs.next()){

			UsersListVO userslist =new UsersListVO();
			String loginid=rs.getString("username");
			int userId = rs.getInt("userid");
			userslist.setLoginid(loginid);
			userslist.setId(userId);
			userslist.setRating("4.5");
			list.add(userslist);

		}

		rs.close();
		smt.close();
		conn.close();


		return list;
	}

	public int getUserId(String strUserid) throws DataAccessException,java.sql.SQLException{

		Connection conn = this.getSession().connection();
		//Write jdbc code to validate the user against database
		Statement smt = conn.createStatement();
		ResultSet rs;
		//write select query for checking password 

		String query="select userid from login where username='"+strUserid+"'";
		rs=smt.executeQuery(query);
		rs.next();

		int id=rs.getInt("userid");


		smt.close();
		rs.close();
		conn.close();
		return id;
	}

	//retrive user forget password
	public String[] retriveUserForgetPassword(String strUserName, String strEmail) throws DataAccessException,java.sql.SQLException{
		//boolean valid = false;




		Connection conn = this.getSession().connection();
		//Write jdbc code to validate the user against database
		Statement smt = conn.createStatement();

		ResultSet rs;
		String query;


		//write select query for checking password 

		if(strUserName != ""){

			query="select password,email,username from login where username='"+strUserName+"'";

		}else{
			query="select password,email,username from login where email='"+strEmail+"'";
		}

		rs=smt.executeQuery(query);



		String[] returnValues = new String[3];  



		while(rs.next()){



			returnValues[0]=rs.getString("password");

			returnValues[1]=rs.getString("email"); 

			returnValues[2]=rs.getString("loginid"); 


		}

		smt.close();
		rs.close();
		conn.close();

		if(returnValues[0] != null){

			return returnValues;

		}	else{
			String[] errorValues = new String[2];  
			errorValues[0]="error";
			return errorValues;
		}
	}


	
	public void addquestion(int seekerid, int providerid, String question) throws SQLException
	{
		
		Connection conn = this.getSession().connection();
		//Write jdbc code to validate the user against database
		Statement smt = conn.createStatement();
		ResultSet rs;
		//write select query for checking password 

		String query="INSERT INTO `expertadvise`.`questions` (`questions`, `seekerid`, `providerid`) VALUES (" +
				"			'" + question + "'," + seekerid + ","+providerid + ")"  ;
		int i =smt.executeUpdate(query);
		
		
	}


	
	public void addanswer(String struserid, String questionid, String stranswer) 
	{
		Connection conn = this.getSession().connection();
		//Write jdbc code to validate the user against database
		Statement smt;
		try {
			smt = conn.createStatement();
		
		ResultSet rs;
		//write select query for checking password 

		String query="Update  `expertadvise`.`questions` " +
					  "SET  `providerid`=" + struserid + 
					  ", answers = '" + stranswer + 
					  "'  WHERE questionsid = "+questionid   ;
		System.out.println(query);
		int i =smt.executeUpdate(query);
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}




}
